from datetime import datetime

import openpyxl
from openpyxl.styles import Alignment, Border, Side
from util import to_str, to_int


class excel_db:

    # path = 'resource/租房信息2.xlsm'
    # sheet = '租客'

    def __init__(self):
        self.path = 'resource/租房信息.xlsm'
        self.sheet = '租客'

    def select_all(self, remove_empty_lines=False):
        data = []  # 用来存放提取的数据
        try:
            wb = openpyxl.open(self.path)  # 获取表格对象
            ws = wb[self.sheet]  # 获取sheet对象
            for index, row in enumerate(ws.iter_rows(min_row=3, min_col=2, max_col=9, values_only=True)):
                # 移除每个单元格值中的前后空格
                cleaned_row = [cell.strip() if isinstance(cell, str) else cell for cell in row]
                cleaned_row.append(index+3)
                # 根据传参，返回不带空行和带空行两种结果
                if remove_empty_lines:
                    if row[7] is not None:
                        data.append(cleaned_row)
                else:
                    data.append(cleaned_row)
        except Exception as e:
            print(f"An error occurred: {e}")
        finally:
            wb.close()
        return data

    def select_index(self, index):
        wb = openpyxl.open(self.path, keep_vba=True)  # 获取表格对象
        ws = wb[self.sheet]  # 获取sheet对象
        row_cells = ws[index][1:9]
        row = [cell.value for cell in row_cells]
        return row

    def select_day(self, day, remove_yizu=True):
        data = self.select_all()
        # 对row[2]进行int格式化，防止数据类型错误
        if remove_yizu:
            filtered_data = [row for row in data if to_int(row[2]) == day and row[6] == '未收租']
        else:
            filtered_data = [row for row in data if to_int(row[2]) == day]
        return filtered_data

    def select_address(self, address):
        data = self.select_all(remove_empty_lines=True)
        filtered_data = [row for row in data if to_str(row[7]).strip() == address and row[0] != None]
        return filtered_data

    def select_free(self):
        data = self.select_all(remove_empty_lines=True)
        filtered_data = [row for row in data if to_str(row[7]).strip() is not None and row[0] is None]
        return filtered_data

    def select_name(self, name):
        data = self.select_all(remove_empty_lines=True)
        filtered_data = [row for row in data if to_str(row[0]).strip() == name]
        return filtered_data

    def exist_address(self, address):
        data = self.select_all(remove_empty_lines=True)
        for row in data:
            if row[7] == address:
                return True
        return False

    def update_db(self, index):
        wb = openpyxl.open(self.path, keep_vba=True)  # 获取表格对象
        ws = wb[self.sheet]  # 获取sheet对象
        ws['H' + str(index)] = '已收租'
        try:
            wb.save(filename=self.path)
        except Exception as e:
            print(f"An error occurred: {e}")
            return False
        return True

    def reset_db(self, day):
        data = self.select_all()
        wb = openpyxl.open(self.path, keep_vba=True)  # 获取表格对象
        ws = wb[self.sheet]  # 获取sheet对象
        for row in data:
            if day == 15:
                if row[2] is not None and to_int(row[2]) <= day:
                    index = data.index(row) + 3
                    ws['H' + str(index)] = '未收租'

            else:
                if row[2] is not None and to_int(row[2]) >= day:
                    index = data.index(row) + 3
                    ws['H' + str(index)] = '未收租'
        try:
            wb.save(filename=self.path)
        except Exception as e:
            print(f"An error occurred: {e}")
            return False
        return True

    def delete_db(self, index):
        wb = openpyxl.open(self.path, keep_vba=True)  # 获取表格对象
        ws = wb[self.sheet]  # 获取sheet对象
        row = [cell.value for cell in ws[index][1:9]]
        for i in range(66, 73):
            ws[chr(i) + str(index)] = None
        try:
            wb.save(filename=self.path)
            wb.close()
            with open('resource/release_log.txt', 'a') as f:
                log = f"{row[7]} {row[0]} {row[1]} {row[2]} {row[3]} {row[4]} {row[5]}  已退房"
                f.write(str(datetime.now().strftime("%Y-%m-%d %H:%M:%S")) + " " + log + '\n')
            return True
        except Exception as e:
            print(f"An error occurred: {e}")
            return False

    def new_db(self, address, room_num):
        try:
            wb = openpyxl.open(self.path, keep_vba=True)  # 获取表格对象
            ws = wb[self.sheet]  # 获取sheet对象
            last_row_number = ws.max_row
            index = last_row_number + 1
            ws.merge_cells(range_string=f'A{index}:H{index}')
            for i in range(room_num):
                ws.append([address, None, None, None, None, None, None, '未收租', address])
            ws.merge_cells(range_string=f'A{index+1}:A{index+room_num}')
            # 设置居中对齐
            center_aligned_text = Alignment(horizontal='center', vertical='center')

            # 应用居中对齐到合并后的单元格
            ws[f'A{index+1}'].alignment = center_aligned_text

            # 创建边框对象，设置为细边框
            thin_border = Border(left=Side(style='thin'),
                                 right=Side(style='thin'),
                                 top=Side(style='thin'),
                                 bottom=Side(style='thin'))

            # 为A1到B3区域的单元格设置边框
            for row in ws[f'A{index+1}:H{index+room_num}']:
                for cell in row:
                    cell.border = thin_border
                    cell.alignment = center_aligned_text

            wb.save(filename=self.path)
            return True
        except Exception as e:
            print(f"An error occurred: {e}")
            return False

    def add_db(self, item):
        data = self.select_all()
        for row in data:
            if to_str(row[7]) == item.address and row[0] is None:
                index = data.index(row) + 3  # 根据前面有几行标题决定
                try:
                    wb = openpyxl.open(self.path, keep_vba=True)  # 获取表格对象
                    ws = wb[self.sheet]  # 获取sheet对象
                    ws[f'B{index}'] = item.name
                    ws[f'C{index}'] = item.date
                    ws[f'D{index}'] = item.date.day
                    ws[f'E{index}'] = item.phone_num
                    ws[f'F{index}'] = item.rent
                    ws[f'G{index}'] = item.note
                    ws[f'H{index}'] = item.rent_status
                    wb.save(filename=self.path)
                    if item.rent_status == '已收租':
                        date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        log = f"{date}'      '{item.address} {item.name} {item.date.day}号已收租, 应收租金{item.rent}元"
                        with open('resource/rent_log.txt', 'a') as f:
                            f.write(log + '\n')
                    return True
                except Exception as e:
                    print(f"An error occurred: {e}")
                    return False
        return 3  # 状态码代表未找到，这里代表没有空房间，需要先退租


# def alter_db(self, index_s, index_d)


if __name__ == '__main__':
    edb = excel_db()
    # data = edb.select_all()
    # data = edb.update_db('139-104', '李言言')
    # data = edb.select_day(22)
    data = edb.select_all(remove_empty_lines=True)
    # for row in data:
    #     print(row)
    # print(edb.select_address('123-404'))

    print(data)